 #!/usr/bin/env python3
"""
从API获取真实风速数据修复数据库中的问题数据
"""

import sys
import os
from datetime import datetime, timedelta
# 添加backend目录到sys.path，确保可以正确导入后端模块
backend_path = os.path.join(os.path.dirname(__file__), 'backend')
if backend_path not in sys.path:
    sys.path.append(backend_path)

from backend.config.database import get_db_session
from backend.entities.weather_daily import WeatherDaily
from backend.service.weather_forecast_service import WeatherForecastService
from backend.utils.weather_api import WeatherAPIService

def get_real_wind_speed_from_api(target_date):
    """从API获取真实风速数据"""
    try:
        # 使用WeatherAPIService获取历史数据
        weather_service = WeatherAPIService()
        
        # 获取目标日期前后3天的数据以确保有数据
        start_date = (target_date - timedelta(days=3)).strftime('%Y-%m-%d')
        end_date = (target_date + timedelta(days=3)).strftime('%Y-%m-%d')
        
        weather_data = weather_service.get_weather_data(start_date, end_date)
        
        if weather_data is not None and not weather_data.empty:
            # 查找目标日期的数据
            target_data = weather_data[weather_data['dt'] == target_date]
            if not target_data.empty:
                # 返回风速数据，如果没有ws_max字段，尝试其他风速字段
                import pandas as pd  # 修复未导入pd的问题
                wind_speed = target_data.iloc[0].get('ws_max')
                if wind_speed is None or pd.isna(wind_speed):
                    # 尝试其他风速字段
                    wind_speed = target_data.iloc[0].get('windspeed')
                return wind_speed
        # 如果WeatherAPIService没有数据，尝试WeatherForecastService
        forecast_service = WeatherForecastService()
        forecast_data = forecast_service.get_weather_forecast(7)
        
        if forecast_data and 'days' in forecast_data:
            for day in forecast_data['days']:
                if day.get('datetime') == target_date.strftime('%Y-%m-%d'):
                    return day.get('windspeed')
        
        return None
        
    except Exception as e:
        print(f"从API获取风速数据失败: {e}")
        return None

def fix_wind_speed_with_api_data():
    """使用API真实数据修复风速"""
    try:
        with get_db_session() as db:
            # 查找所有ws_max为19.1的记录
            problematic_records = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max == 19.1
            ).order_by(WeatherDaily.dt).all()
            
            print(f"找到 {len(problematic_records)} 条风速为19.1的记录")
            
            if not problematic_records:
                print("没有找到需要修复的记录")
                return
            
            fixed_count = 0
            failed_count = 0
            
            for record in problematic_records:
                print(f"正在处理日期: {record.dt}")
                
                # 从API获取真实风速数据
                real_wind_speed = get_real_wind_speed_from_api(record.dt)
                
                if real_wind_speed is not None and real_wind_speed > 0:
                    # 更新风速数据
                    old_speed = record.ws_max
                    # 直接赋值 float 可能导致类型不兼容，需用 SQLAlchemy 的方式赋值
                    record.ws_max = real_wind_speed
                    fixed_count += 1
                    print(f"  修复: {old_speed} -> {record.ws_max}")
                else:
                    failed_count += 1
                    print(f"  无法获取真实数据，保持原值: {record.ws_max}")
                
                # 每处理10条记录提交一次
                if (fixed_count + failed_count) % 10 == 0:
                    db.commit()
                    print(f"已处理 {fixed_count + failed_count} 条记录...")
            
            # 最终提交
            db.commit()
            
            print(f"\n修复完成:")
            print(f"成功修复: {fixed_count} 条记录")
            print(f"修复失败: {failed_count} 条记录")
            
            # 验证修复结果
            remaining_19_1 = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max == 19.1
            ).count()
            print(f"修复后仍有 {remaining_19_1} 条记录的风速为19.1")
            
            # 显示修复后的分布
            from sqlalchemy import func
            wind_speed_stats = db.query(
                WeatherDaily.ws_max,
                func.count(WeatherDaily.ws_max).label('count')
            ).filter(
                WeatherDaily.ws_max.isnot(None)
            ).group_by(WeatherDaily.ws_max).order_by(
                func.count(WeatherDaily.ws_max).desc()
            ).limit(10).all()
            
            print("\n修复后的风速分布（前10个最常见的值）:")
            for ws_max, count in wind_speed_stats:
                print(f"风速 {ws_max}: {count} 条记录")
                
    except Exception as e:
        print(f'修复失败: {e}')
        import traceback
        traceback.print_exc()

def check_api_availability():
    """检查API可用性并与weather_daily表结构兼容性测试"""
    try:
        print("检查API可用性及与weather_daily表兼容性...")
        
        # 测试WeatherAPIService
        weather_service = WeatherAPIService()
        test_date = datetime.now().date() - timedelta(days=7)
        start_date = (test_date - timedelta(days=3)).strftime('%Y-%m-%d')
        end_date = (test_date + timedelta(days=3)).strftime('%Y-%m-%d')
        
        print(f"测试日期范围: {start_date} 到 {end_date}")
        
        weather_data = weather_service.get_weather_data(start_date, end_date)
        
        if weather_data is not None and not weather_data.empty:
            print(f"WeatherAPIService 可用，获取到 {len(weather_data)} 条数据")
            print(f"API数据列: {list(weather_data.columns)}")
            
            # 检查API数据是否包含weather_daily表需要的字段
            required_fields = ['dt', 'ws_max']  # weather_daily表的关键字段
            missing_fields = [field for field in required_fields if field not in weather_data.columns]
            
            if missing_fields:
                print(f"警告: API数据缺少weather_daily表需要的字段: {missing_fields}")
                print("尝试查找替代字段...")
                
                # 检查是否有替代字段
                if 'ws_max' not in weather_data.columns and 'windspeed' in weather_data.columns:
                    print("找到替代字段: 'windspeed' 可用于 'ws_max'")
                elif 'dt' not in weather_data.columns and 'datetime' in weather_data.columns:
                    print("找到替代字段: 'datetime' 可用于 'dt'")
                else:
                    print("未找到合适的替代字段")
                    return False
            
            # 测试数据格式兼容性
            sample_data = weather_data.iloc[0] if len(weather_data) > 0 else None
            if sample_data is not None:
                print(f"样本数据: {sample_data.to_dict()}")
                
                # 检查日期格式
                if 'dt' in weather_data.columns:
                    dt_sample = sample_data.get('dt')
                    print(f"日期字段示例: {dt_sample} (类型: {type(dt_sample)})")
                
                # 检查风速字段
                if 'ws_max' in weather_data.columns:
                    ws_sample = sample_data.get('ws_max')
                    print(f"风速字段示例: {ws_sample} (类型: {type(ws_sample)})")
                elif 'windspeed' in weather_data.columns:
                    ws_sample = sample_data.get('windspeed')
                    print(f"风速字段示例: {ws_sample} (类型: {type(ws_sample)})")
            
            # 检查数据库中是否有对应的记录
            with get_db_session() as db:
                # 检查weather_daily表结构
                from sqlalchemy import inspect, func
                inspector = inspect(db.bind)
                if inspector is not None:
                    table_columns = [col['name'] for col in inspector.get_columns('weather_daily')]
                    print(f"weather_daily表字段: {table_columns}")
                else:
                    print("无法获取表结构信息")
                
                # 检查是否有测试日期范围内的数据
                test_records = db.query(WeatherDaily).filter(
                    WeatherDaily.dt >= start_date,
                    WeatherDaily.dt <= end_date
                ).count()
                print(f"数据库中测试日期范围内的记录数: {test_records}")
                
                # 检查风速数据分布
                wind_speed_stats = db.query(
                    WeatherDaily.ws_max,
                    func.count(WeatherDaily.ws_max).label('count')
                ).filter(
                    WeatherDaily.ws_max.isnot(None)
                ).group_by(WeatherDaily.ws_max).order_by(
                    func.count(WeatherDaily.ws_max).desc()
                ).limit(5).all()
                
                print("数据库中风速数据分布（前5个最常见的值）:")
                for ws_max, count in wind_speed_stats:
                    print(f"  风速 {ws_max}: {count} 条记录")
            
            return True
        else:
            print("WeatherAPIService 不可用")
            return False
            
    except Exception as e:
        print(f"API检查失败: {e}")
        import traceback
        traceback.print_exc()
        return False

if __name__ == '__main__':
    print("开始从API获取真实风速数据修复数据库...")
    
    # 检查API可用性
    if not check_api_availability():
        print("API不可用，无法进行修复")
        sys.exit(1)
    
    # 确认操作
    print("\n警告: 此操作将修改数据库中的风速数据")
    confirm = input("确认继续? (y/N): ").strip().lower()
    
    if confirm == 'y':
        fix_wind_speed_with_api_data()
    else:
        print("操作已取消")